package com.zhijs.struts.action;

import static java.lang.System.out;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.commons.collections.map.MultiValueMap;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.struts2.interceptor.SessionAware;

import com.h819.core.db.MyDbUtils;
import com.h819.core.web.jqgrid.JqgridListBaseAction;
import com.h819.core.web.jqgrid.JqgridUtils;

;

/**
 * @ClassName: ShowListBydepartment
 * @Description: TODO(这里用一句话描述这个类的作用)
 * @author h819
 * @date 2010-1-7 下午03:38:42
 * @version V1.0
 */

/**
 * 实现方法同 ShowUpdateAction
 */

public class ShowListByDepartmentAction extends JqgridListBaseAction implements
		SessionAware {

	private Logger log = Logger.getLogger(ShowListByDepartmentAction.class);

	Map session;

	private String departmentid;
	private QueryRunner runner = new QueryRunner();;
	private Connection conn = null;

	com.h819.core.web.jqgrid.Page p = this.getPage();

	int totalRecords;

	// 每页记录数
	// int pageSize = conf.getInt("pagesize");// 每页显示记录数，默认为10条;
	// 默认当前页
	int defaultPage = 1;
	// 当前页数
	int page = 0;

	public String execute() throws Exception {

		log.info(" departmentid from request :" + this.getDepartmentid());

		if (this.isSearch()) {
			search();

		} else {
			// 准备数据
			prepareListDate(null, null);
		}

		// 以下变量，必须赋值
		this.setCurPageNo(p.getPageNo());
		this.setTotalPages(p.getTotalPages());
		this.setTotalRecords(p.getTotalCount());
		this.setDataRows(this.getDataRows());

		// 测试结果
		log.info("当前页号：" + p.getPageNo());
		log.info("每页显示数据数量：" + p.getPageSize());
		log.info("数据总量：" + p.getTotalCount());
		log.info("排序变量：" + p.getOrderBy());
		log.info("升序/降序：" + p.getOrder());

		// 测试数据
		for (Map m : this.getDataRows()) {
			StringUtils.center("当前页面的数据 ", 80, '*');
			for (Iterator it = m.entrySet().iterator(); it.hasNext();) {
				Map.Entry entry = (Map.Entry) it.next();
				// System.out.println(entry.getKey() + " = " +
				// entry.getValue());
			}
		}

		return SUCCESS;
	}

	/**
	 * 按照部门，返回该部门所有托管标准列表
	 */
	private void prepareListDate(String st_standard_code, String oper) {

		conn = MyDbUtils.getInstance("h819.properties").getOracleConnection235();

		try {

			// 组装查询条件
			String subsql = "";
			String departmentid = this.getDepartmentid();
			if (!departmentid.equals("0")) {
				if (st_standard_code == null)
					subsql = "where zjs.st_department ='"
							+ this.getDepartmentid() + "'";

				if (st_standard_code != null
						&& (oper.equals("=") || oper.equals("<>")))
					subsql = "where zjs.st_department ='"
							+ this.getDepartmentid() + "' and st_standardcode "
							+ oper + " '" + st_standard_code + "'";

				if (st_standard_code != null
						&& (oper.equals("like") || oper.equals("not like")))
					subsql = "where zjs.st_department ='"
							+ this.getDepartmentid() + "' and st_standardcode "
							+ oper + " '%" + st_standard_code + "%'";

			} else {// 所有部门

				if (st_standard_code != null
						&& (oper.equals("=") || oper.equals("<>")))
					subsql = "where  st_standardcode " + oper + " '"
							+ st_standard_code + "'";

				if (st_standard_code != null
						&& (oper.equals("like") || oper.equals("not like")))
					subsql = "where st_standardcode " + oper + " '%"
							+ st_standard_code + "%'";

			}

			// 计算记录数目
			String queryCount = "select count(1) "
					+ "from zhijs_standard_view zjs " + subsql;

			log.info("queryCount : " + queryCount);

			// 计算记录总数
			Map<String, Object> r7 = runner.query(conn, queryCount,
					new MapHandler());
			for (Iterator it = r7.entrySet().iterator(); it.hasNext();) {
				Map.Entry entry = (Map.Entry) it.next();
				totalRecords = Integer.parseInt(entry.getValue().toString());
				// out.println(entry.getKey() + " = " + entry.getValue());
			}

			// 给 page 设置必要参数
			p.setTotalCount(totalRecords);

			// 计算分页
			int begin = p.getFirst();
			int end = begin + p.getPageSize() - 1;
			if (end >= this.getTotalRecords())
				end = Long.valueOf(this.getTotalRecords()).intValue();
			// 起始位置
			String limitFrom = String.valueOf(begin);
			// 结束位置
			String limitTo = String.valueOf(end);

			log.info("in zhijs : limitFrom = " + limitFrom);
			log.info("in zhijs : limitTo = " + limitTo);

			// 按照部门查询
			String queryListByDepartment = "select temptable.*  from( "
					+ "select  row_number() over(order by zjs.zhijs_departmentcode) as num, "
					+ "zjs.* " + "from zhijs_standard_view zjs " + subsql + ")"
					+ "temptable where  num between " + limitFrom + " AND "
					+ limitTo + "  order by st_type";

			log.info("queryListByDepartment = " + queryListByDepartment);

			List<Map<String, Object>> listByDepartmentTemp;
			List<Map<String, Object>> stModifyTemp;

			listByDepartmentTemp = runner.query(conn, queryListByDepartment,
					new MapListHandler());

			// 重新构造返回值，每次都重新初始化
			List<Map<String, Object>> newSearchData = new ArrayList<Map<String, Object>>();

			// 用于存放关系查询新标准：标准号、文本地址和实施日期
			Map<String, Object> runnerResultStandard;

			// 用于存放文本信息
			ArrayList<MultiValueMap> textAddress = new ArrayList<MultiValueMap>();

			// 记录序号，和 NUM 不同，此 id 用来记录结果集的排序号，可以区分选择了哪条记录.下载文件的时候用的此变量
			int id = 0;
			if (listByDepartmentTemp != null)
				for (Map standardMap : listByDepartmentTemp) {// 查询所有的标准,每次循环是一条标准题录
					// log.info(StringUtils.center("Standard by department list  QueryRunner",
					// 80, '*'));
					id++;
					// 临时变量，重新组合数据用重新构造返回值
					Map<String, Object> tempStandardMap = new TreeMap<String, Object>();
					// 存放找到的文本地址信息,和 ArrayList<Map<String, String>> textAddress
					// 配合使用
					// 设计成可以存放多值value的MultiValueMap，这样一个标准号，可以对应多个 修改单地址了
					MultiValueMap tempMultiMap = new MultiValueMap();

					// TreeMap<String, Object> temptm = new TreeMap<String,
					// Object>();

					for (Iterator it = standardMap.entrySet().iterator(); it
							.hasNext();) { // 每条标准的具体信息
						Map.Entry entry = (Map.Entry) it.next();

						// log.info(entry.getKey() + " = " + entry.getValue());

						// 查询标准库的信息
						if (entry.getKey().toString().equals("ST_STANDARDCODE")) {

							// ===== 和平台标准库关联，查询托管标准的相关信息
							String searchStandardsql = "select st.standard_code as st_standard_code ,st.namecn as st_standard_name, st.text_address as st_standard_text_address,st.act_time as st_standard_act_time  from st_standard st where st.standard_code ='"
									+ entry.getValue().toString().trim() + "'";
							// 新标准信息,只会有一条，故用 MapHandler()
							// log.info("search Standard in st_standard table : "
							// + searchStandardsql);
							runnerResultStandard = runner.query(conn,
									searchStandardsql, new MapHandler());

							if (runnerResultStandard != null)// runner的返回值是null，需要判断
								for (Iterator it9 = runnerResultStandard
										.entrySet().iterator(); it9.hasNext();) { // 提取该信息
									Map.Entry entry9 = (Map.Entry) it9.next();
									// out.println(entry9.getKey() + " = "
									// + entry9.getValue());
									if (entry9.getKey().toString().equals(
											"ST_STANDARD_ACT_TIME")) { // 重新设置日期，这里可能设定格式化，以后再研究
										if (entry9.getValue() != null)
											tempStandardMap.put(entry9.getKey()
													.toString(), StringUtils
													.substringBefore(entry9
															.getValue()
															.toString(),
															"T00:00:00"));
									} else if (entry9.getKey().toString()
											.equals("ST_STANDARD_TEXT_ADDRESS")) {// 保存文本地址

										// 由于都转换为了 String 类型，需要判断空值
										String value = "";
										if (entry9.getValue() != null)
											value = entry9.getValue()
													.toString();

										tempMultiMap.put(entry9.getKey()
												.toString(), value);

										tempStandardMap.put(entry9.getKey()
												.toString(), "浏览");

									} else if (entry9.getKey().toString()
											.equals("ST_STANDARD_NAME")) {// 保存题录名称

										// 由于都转换为了 String 类型，需要判断空值
										String value = "";
										if (entry9.getValue() != null)
											value = entry9.getValue()
													.toString();

										tempStandardMap.put(entry9.getKey()
												.toString(), value);

									}

									else
										tempStandardMap.put(entry9.getKey()
												.toString(), entry9.getValue());

								}// 和平台标准库关联，查询托管标准的相关信息 ，完毕

							// ============= 和平台标准库关联，查询托管标准的修改单信息

							String stModify = "select  stmod.modify_id,stmod.standard_code,stmod.modify_number,stmod.file_address from st_modify stmod where stmod.standard_code ='"
									+ entry.getValue().toString() + "'";

							// log.info("search Standard in st_modify table : "
							// + stModify);
							stModifyTemp = runner.query(conn, stModify,
									new MapListHandler());
							if (stModifyTemp != null)// runner 的返回值是null，需要判断
								for (Map mm : stModifyTemp) {
									for (Iterator itstMod = mm.entrySet()
											.iterator(); itstMod.hasNext();) {
										Map.Entry entrystMod = (Map.Entry) itstMod
												.next();
										out.println(entrystMod.getKey() + " = "
												+ entrystMod.getValue());

										if (entrystMod.getKey().toString()
												.equals("FILE_ADDRESS")) {// 保存修改单地址信息

											// 由于都转换为了 String 类型，需要判断空值
											String value = "";
											if (entrystMod.getValue() != null)
												value = entrystMod.getValue()
														.toString();
											tempMultiMap
													.put(entrystMod.getKey()
															.toString(), value);
										}

									}

								}// 提取修改单

							if (stModifyTemp != null
									&& stModifyTemp.size() != 0)
								tempStandardMap.put("stModifySum", stModifyTemp
										.size()
										+ ",  查看");

							// =====查询修改单详细信息完毕。

							// ZHIJS_STTANDARDCODE 也放入 map
							tempStandardMap.put(entry.getKey().toString(),
									entry.getValue());

							String value = "";
							if (entry.getValue() != null)
								value = entry.getValue().toString();
							tempMultiMap.put(entry.getKey().toString(), value);

						}// 平台库查询完成

						else if (entry.getKey().toString().equals("NUM")) {// 序号

							// 由于都转换为了 String 类型，需要判断空值
							String value = "";
							if (entry.getValue() != null)
								value = entry.getValue().toString();

							tempMultiMap.put(entry.getKey().toString(), value);

							tempStandardMap.put(entry.getKey().toString(),
									entry.getValue());

						} else if (entry.getKey().toString().equals(
								"ST_LASTUPDATE")) { // 重新设置日期，这里可能设定格式化，以后再研究
							tempStandardMap.put(entry.getKey().toString(),
									StringUtils.substringBefore(entry
											.getValue()

											.toString(), "T00:00:00"));
						} else if (entry.getKey().toString().equals(
								"ZHIJS_DEPARTMENTCODE")) { // 重新设置日期，这里可能设定格式化，以后再研究
							// 由于都转换为了 String 类型，需要判断空值

							String value = "";
							if (entry.getValue() != null)
								value = entry.getValue().toString();

							tempMultiMap.put(entry.getKey().toString(), value);

							tempStandardMap.put(entry.getKey().toString(),
									entry.getValue());
						}

						else {
							// 其他字段不做处理
							tempStandardMap.put(entry.getKey().toString(),
									entry.getValue());
							// tempMap.put("ACTION", "更新");
						}

					}// 查询单条托管标准详细信息完成

					tempMultiMap.put("id", Integer.valueOf(id).toString());
					newSearchData.add(tempStandardMap);
					textAddress.add(tempMultiMap);

				}// 逐条查询托管标准完成

			this.setDataRows(newSearchData);
			log.info("newSearchData size :" + newSearchData.size());
			log.info("textAddress size :" + textAddress.size());

			// 放入 session ，用于显示更新总数
			session.put("totalRecords_department", totalRecords);
			// 放入 session ，下载文件时,再次检索
			session.put("resultdata", textAddress);

		} catch (SQLException e) {
			DbUtils.printStackTrace(e);
		} finally {
			DbUtils.closeQuietly(conn);
		}
	}

	/**
	 * search 操作，仅提供 标准号搜索
	 */
	private void search() {

		log.info("in search action:");
		log.info("search filed:" + this.getSearchField());
		log.info("search string:" + this.getSearchString());
		log.info("search oper:"
				+ JqgridUtils.getOperSqlName(this.getSearchOper()));

		this.prepareListDate(this.getSearchString().trim(), JqgridUtils
				.getOperSqlName(this.getSearchOper()));

	}

	/*
	 * 以下getter方法必须实现，struts2-json插件会将这些getter方法序列化，以便输出json对象。
	 * 这些getter方法只需返回相应的属性即可。如getCurPage应该返回curPage （curPage在JsonBaseAction中有定义）
	 */
	@Override
	public int getCurPageNo() {
		return this.curPageNo;
	}

	@Override
	public List<Map<String, Object>> getDataRows() {
		return this.dataRows;
	}

	@Override
	public long getTotalPages() {
		return this.totalPages;
	}

	@Override
	public long getTotalRecords() {
		return this.totalRecords;
	}

	public void setSession(Map session) {
		this.session = session;
	}

	public String getDepartmentid() {
		return departmentid;
	}

	public void setDepartmentid(String departmentid) {
		this.departmentid = departmentid;
	}

}
